﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Modellayer;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;

namespace Dblayer
{
    public class DbPerson
    {
        
        private SqlCommand cmd = null;


        public DbPerson()
        {
            
        }


        public void createPerson(Person p)
        {
            try
            {
                Connection.dbconn.Open();

                //Person p = new Person(phoneNumber,email,password,name,loginId,type);

                string insert = @"insert into Person(PhoneNumber, Name, Email, Password, PersonType, LoginId) 
                    values ('" + p.phoneNumber + "'," + "'" + p.email + "'," + "'" + p.password + "'," + "'" + 
                               p.name + "'," + "'" + p.loginId + "'," + "'" + p.type + "')";

                cmd = Connection.GetDBCommand(insert);
                cmd.ExecuteReader();
                
            }
            catch (System.Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.dbconn.Close();
                }
            }
            
        }

        public Person findPerson(int phoneNumber)
        {
            string person = @"SELECT * FROM Person WHERE PhoneNumber = " + phoneNumber;
            return buildPerson(person);
        }

        public List<Person> findAllPersons()
        {
            string find = @"SELECT * FROM Person";
            return buildfindAllPersons(find);
        }

        public List<Person> buildfindAllPersons(string sql)
        {
            List<Person> pers = new List<Person>();
            System.Data.IDataReader dbReader;
            try
            {
                Connection.dbconn.Open();

                cmd = Connection.GetDBCommand(sql);
                dbReader = cmd.ExecuteReader();
                while (dbReader.Read())
                {

                    Person p = new Person();
                        p.phoneNumber = (int)dbReader["PhoneNumber"];
                        p.name = (string)dbReader["Name"];
                        p.email = (string)dbReader["email"];
                        p.password = (string)dbReader["Password"];
                        p.type = (string)dbReader["Persontype"];
                        p.loginId = (string)dbReader["loginId"];

                        pers.Add(p);
                    
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.dbconn.Close();
                }
            }
            return pers;
        }



        private Person buildPerson(string person)
        {
            System.Data.IDataReader dbReader;
            Person p = null;

            try
            {
                
                cmd = Connection.GetDBCommand(person);
                dbReader = cmd.ExecuteReader();
                if (dbReader.Read())
                {

                    p = new Person
                    ((int)dbReader["PhoneNumber"],
                    (string)dbReader["Name"],
                    (string)dbReader["email"],
                    (string)dbReader["Password"],
                    (string)dbReader["Persontype"],
                    (string)dbReader["loginId"]);
                    p.phoneNumber = (int)dbReader["PhoneNumber"];
                    
                    
                }
                Connection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Find Customer failed!" + e.Message);
                p = null;
            }
            return p;
        }

        public void updateEmployee(Person p)
        {
            try
            {
                Connection.dbconn.Open();

                string update = @"UPDATE Person SET " + " PhoneNumber = '" + p.phoneNumber + "'," + " Email ='" + p.email + "'," + " Password ='" +
                    p.password + "'," + "Name ='" + p.name + "'," + "LoginId ='" + p.loginId + "')";
                cmd = Connection.GetDBCommand(update);
                cmd.ExecuteReader();

            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.dbconn.Close();
                }
            }
        }

        public void updateCustomer(Person p)
        {
            try
            {
                Connection.dbconn.Open();

                string update = @"UPDATE Person SET " + " PhoneNumber = '" + p.phoneNumber + "'," + " Email ='" + p.email + "'," + " Password ='" +
                        p.password + "'," + "Name ='" + p.name + "'";
                cmd = Connection.GetDBCommand(update);
                cmd.ExecuteReader();
            }
            catch (Exception e)
            {

                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.dbconn.Close();
                }
            }
        }


        //public void ReadData()
        //{
        //    SqlDataReader reader = null;

        //    try
        //    {
        //        con.Open();
        //        SqlCommand cmd = new SqlCommand("select * from Persons");
        //        reader = cmd.ExecuteReader();
        //        while (reader.Read())
        //        {
        //            for(int i = 0; i < reader.FieldCount; i++)
        //            {
        //                Console.Write("{0} = {1}",
        //                reader.GetName(i),
        //                reader.GetValue(i).ToString().Trim());
        //            }
        //        }
        //    }
        //    finally
        //    {
        //        if (reader != null)
        //        {
        //            reader.Close();
        //        }

        //        if (con != null)
        //        {
        //            con.Close();
        //        }
        //    }
        //}
    }
}
